/* File: acquisitions_summary_stats.do
 * Author: Luca Maini
 * Purpose: calculates some summary stats about deals
 * Paper Output: Table OA1, Table 1, Figure 2, Table A1
 * Presentation Output: acquisitions summary figure
 *
 * Date Created: 09/15/2021
 *
 */

* Get launch date info (to merge in later)
use "${maindir}\combined_regression_dataset_with_Valeant_augmented.dta", clear
keep Product mkt_start
duplicates drop
gen launch_year = yofd(mkt_start)
drop mkt_start

tempfile launch_date
save `launch_date', replace
 
* open data (start from overlap measures)
use "${maindir}\drug_overlap_measures.dta", clear

keep dealID Product acqd_drug_same_atc3

* merge with full acquisition data
merge 1:1 dealID Product using "${maindir}\acquisitionsClean_selected.dta", keep(using match) nogen

replace acqd_drug_same_atc3 = 0 if acqd_drug_same_atc3 == .	// missing means 0 here
bysort dealID : egen overlap_atc3 = max(acqd_drug_same_atc3)

* merge with deal characteristics data
merge m:1 dealID using "${maindir}\acquisition_characteristics.dta", nogen ///
	keepusing(company_acq large_deal lg_acq valeant ///
			  tot_drug_postAcq_net_sales tot_drug_postAcq_net_sales ///
			  stlth tot_Acquiror_net_sales tot_Target_net_sales)

* merge in launch date info
merge m:1 Product using `launch_date', keep(master match) nogen

gen age = yofd(dofq(acquisitionQrt)) - launch_year
gen y2LOE = yofd(LOE) - yofd(dofq(acquisitionQrt))

* create sample selection table
file open tableOA1_1 using "${paperdir}\Tables\Table OA1_1.csv", write replace
file write tableOA1_1 ",Individual deals, Drug-deal combinations" _n

* total number of deals and drugs
unique dealID
local deals = r(sum)
unique Product
file write tableOA1_1 "Total,`deals',`r(sum)', `r(N)'" _n

preserve	// do this so we can print Valeant deal statistics later

* drop valeant
drop if AcquirorName == "VALEANT PHARMACEUTICALS" | ///
		TargetName == "VALEANT PHARMACEUTICALS"

* Drop Relistor deal	// not a Valeant deal, but Relistor owned by Valeant, so 
* dropped from the main sample
drop if dealID == 217

* deals without Valeant
unique dealID
local deals = r(sum)
unique Product
file write tableOA1_1 "Excluding Valeant,`deals',`r(sum)', `r(N)'" _n
file close tableOA1_1

* gen indicator for deal
bysort dealID : gen ind_deal = _n == 1


////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////
////////																////////
////////								PAPER							////////
////////																////////
////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////

//// 	Numbers in main text	////

* Number of deals
unique dealID

* number of unique Products
unique Product

* number of Products acquired more than once
bysort Product : gen tot = _N
unique Product if tot > 1
drop tot

* Number of actually overlapping products in an acquisition above the HSR threshold
bysort dealID : egen num_overlap = sum(acqd_drug_same_atc3)
sum num_overlap if ind_deal == 1 & overlap_atc3 == 1 & stlth == 0

////////////////////////
////				////
////	TABLE 1		////
////				////
////////////////////////

file open table1 using "${paperdir}\Tables\Table 1.csv", write replace

file write table1 ",,Overall,Horizontal acquisitions,Horizontal acquisitions below the HSR threshold,Cross-market acquisitions by larger company," _n
file write table1 "Deal-level" _n

* first line: count of deals
qui count if ind_deal == 1
local tot = r(N)
qui count if ind_deal == 1 & overlap_atc3 == 1 & stlth == 0
local hor = r(N)
qui count if ind_deal == 1 & overlap_atc3 == 1 & stlth == 1
local horSm = r(N)
qui count if ind_deal == 1 & lg_acq == 1
local large = r(N)

file write table1 "Count,,`tot',`hor',`horSm',`large'" _n

* Next two lines: means and medians for deal size
qui sum ValueofTransactionMil if ind_deal == 1, d
local totMean = round(r(mean))
local totMedian = round(r(p50))
qui sum ValueofTransactionMil if ind_deal == 1 & overlap_atc3 == 1 & stlth == 0, d
local horMean = round(r(mean))
local horMedian = round(r(p50))
qui sum ValueofTransactionMil if ind_deal == 1 & overlap_atc3 == 1 & stlth == 1, d
local horSmMean = round(r(mean))
local horSmMedian = round(r(p50))
qui sum ValueofTransactionMil if ind_deal == 1 & lg_acq == 1, d
local largeMean = round(r(mean))
local largeMedian = round(r(p50))


file write table1 "Value of transaction ($ mill.),mean,`totMean',`horMean',`horSmMean',`largeMean'" _n
file write table1 ",median,`totMedian',`horMedian',`horSmMedian',`largeMedian'" _n

* Next two lines: number of acquired products
qui sum num_products if ind_deal == 1, d
local totMean : display %9.2f r(mean)
local totMedian : display %9.2f r(p50)
qui sum num_products if ind_deal == 1 & overlap_atc3 == 1 & stlth == 0, d
local horMean : display %9.2f r(mean)
local horMedian : display %9.2f r(p50)
qui sum num_products if ind_deal == 1 & overlap_atc3 == 1 & stlth == 1, d
local horSmMean : display %9.2f r(mean)
local horSmMedian : display %9.2f r(p50)
qui sum num_products if ind_deal == 1 & lg_acq == 1, d
local largeMean : display %9.2f r(mean)
local largeMedian : display %9.2f r(p50)

file write table1 "Number of products,mean,`totMean',`horMean',`horSmMean',`largeMean'" _n
file write table1 ",median,`totMedian',`horMedian',`horSmMedian',`largeMedian'" _n

file write table1 "Product-level" _n

* Number of deals (product-level)
qui count
local totD = r(N)
qui count if acqd_drug_same_atc3 == 1 & stlth == 0
local horD = r(N)
qui count if acqd_drug_same_atc3  == 1 & stlth == 1
local horSmD = r(N)
qui count if lg_acq == 1 & acqd_drug_same_atc3 == 0
local largeD = r(N)

file write table1 "Count,,`totD',`horD',`horSmD',`largeD'" _n

* Next: age of products at acquisition (both from launch date and from LOE)
qui sum age, d
local totMean : display %9.2f r(mean)
local totMedian : display %9.2f r(p50)
qui sum age if acqd_drug_same_atc3 == 1 & stlth == 0, d
local horMean : display %9.2f r(mean)
local horMedian : display %9.2f r(p50)
qui sum age if acqd_drug_same_atc3 == 1 & stlth == 1, d
local horSmMean : display %9.2f r(mean)
local horSmMedian : display %9.2f r(p50)
qui sum age if lg_acq == 1 & acqd_drug_same_atc3 == 0, d
local largeMean : display %9.2f r(mean)
local largeMedian : display %9.2f r(p50)

file write table1 "Product age at acquisition,mean,`totMean',`horMean',`horSmMean',`largeMean'" _n
file write table1 ",median,`totMedian',`horMedian',`horSmMedian',`largeMedian'" _n

* Add a few "unreported" numbers (not in the table but in the text of the paper)
qui sum y2LOE, d
local totMean : display %9.2f r(mean)
local totMedian : display %9.2f r(p50)
qui sum y2LOE if acqd_drug_same_atc3 == 1 & stlth == 0, d
local horMean : display %9.2f r(mean)
local horMedian : display %9.2f r(p50)
qui sum y2LOE if acqd_drug_same_atc3 == 1 & stlth == 1, d
local horSmMean : display %9.2f r(mean)
local horSmMedian : display %9.2f r(p50)
qui sum y2LOE if lg_acq == 1 & acqd_drug_same_atc3 == 0, d
local largeMean : display %9.2f r(mean)
local largeMedian : display %9.2f r(p50)

file write table1 "Years to LOE,mean,`totMean',`horMean',`horSmMean',`largeMean'" _n
file write table1 ",median,`totMedian',`horMedian',`horSmMedian',`largeMedian'" _n


file close table1

////////////////////////////////
////////				////////
////////	FIGURE 2	////////
////////				////////
////////////////////////////////

* graph of all acquisitions
gen v_acq = tot_Acquiror_net_sales/10^3
gen v_acq1 = tot_Acquiror_net_sales/10^3 if overlap_atc3 == 1
gen v_acq2 = tot_Acquiror_net_sales/10^3 if overlap_atc3 == 0

gen v_tar = tot_Target_net_sales/10^3

graph set window fontface "${paperFont}"
twoway 	(scatter v_acq1 v_acq2 v_tar [w=tot_drug_postAcq_net_sales] ///
			if ind_deal == 1, msymbol(Oh Oh) mlcolor("${coral}" "${alice}")), ///
	xtitle("Target yearly net sales (billion USD)", height(5)) ///
	xlabel(#8, labsize(small)) ///
	ytitle("Acquirer yearly net sales (billion USD)") ///
	ylabel(#8, labsize(small) angle(0) nogrid) ///
	legend(order(1 "Horizontal acquisitions" ///
				 2 "Cross-market acquisition") ring(0) bplacement(1) cols(1) ///
		   region(color(none)) size(medsmall))
graph export "${paperdir}\Figures\Figure 2.pdf", as(pdf) replace
graph export "${paperdir}\Figures\Figure 2.eps", as(eps) replace


////////	VALEANT ACQUISITIONS TABLE	////////

*** Valeant statistics
restore

preserve	// again, for later tables with other companies

* keep only valeant
keep if AcquirorName == "VALEANT PHARMACEUTICALS" | ///
		TargetName == "VALEANT PHARMACEUTICALS"

* gen indicator for deal
bysort dealID : gen ind_deal = _n == 1


file open tableA1 using "${paperdir}\Tables\Table A1.csv", write replace

file write tableA1 ",,Acquisitions by Valeant,Acquisitions of Valeant products" _n

* first line: count of deals
qui count if ind_deal == 1 & valeant == 1
local val = r(N)
qui count if ind_deal == 1 & valeant == 0
local noval = r(N)

file write tableA1 "Count,,`val',`noval'" _n

* Next two lines: means and medians for deal size
qui sum ValueofTransactionMil if ind_deal == 1 & valeant == 1, d
local valMean = round(r(mean))
local valMedian = round(r(p50))
qui sum ValueofTransactionMil if ind_deal == 1 & valeant == 0, d
local novalMean = round(r(mean))
local novalMedian = round(r(p50))

file write tableA1 "Value of transaction ($ mill.),mean,`valMean',`novalMean'" _n
file write tableA1 ",median,`valMedian',`novalMedian'" _n

* Next two lines: number of acquired products
qui sum num_products if ind_deal == 1 & valeant == 1, d
local valMean : display %9.2f r(mean)
local valMedian : display %9.2f r(p50)
qui sum num_products if ind_deal == 1 & valeant == 0, d
local novalMean : display %9.2f r(mean)
local novalMedian : display %9.2f r(p50)

file write tableA1 "Number of products,mean,`valMean',`novalMean'" _n
file write tableA1 ",median,`valMedian',`novalMedian'" _n

file close tableA1


//////// Numbers for overlap test (Section 4.4)

* get list of in-sample products
use "${maindir}\combined_regression_dataset_augmented.dta", clear
keep Product
duplicates drop

tempfile products
save `products', replace

* open data (start from overlap measures)
use "${outdir}\drug_overlap_class_measures.dta", clear
keep dealID Product acqd_drug_same_class
keep if acqd_drug_same_class == 1

tempfile class
save `class', replace

use "${outdir}\drug_overlap_indication_measures.dta", clear
keep dealID Product acqd_drug_same_indication
keep if acqd_drug_same_indication == 1

tempfile indication
save `indication', replace

use "${maindir}\drug_overlap_measures.dta", clear

keep dealID Product acqd_drug_same_atc3 acqd_drug_same_atc4
keep if acqd_drug_same_atc3 == 1

* merge all overlap indicators together
merge 1:1 Product dealID using `class', keep(master match) nogen
merge 1:1 Product dealID using `indication', keep(master match) nogen

replace acqd_drug_same_class = 0 if acqd_drug_same_class == .
replace acqd_drug_same_indication = 0 if acqd_drug_same_indication == .

* only list of in-sample products
merge m:1 Product using `products', keep(match) nogen

// * merge with full acquisition data
merge 1:m dealID Product using "${maindir}\acquisitionsClean_selected.dta", ///
	keep(master match) keepusing(AcquirorName TargetName) nogen

* merge with deal characteristics data
merge m:1 dealID using "${maindir}\acquisition_characteristics.dta", ///
	keep(master match) keepusing(stlth) nogen

* drop valeant
drop if AcquirorName == "VALEANT PHARMACEUTICALS" | ///
		TargetName == "VALEANT PHARMACEUTICALS"

sum acqd_drug_same_* if stlth == 1
sum acqd_drug_same_* if stlth == 0
